package dao.impl;

import dao.BookDao;
import pojo.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoimpl implements BookDao {
    @Override
    public List<Book> getAllBook() {
        List<Book> list = new ArrayList<Book>();
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);
            //3.构造查询命令并执行命令
            String sql = "select * from books";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list.clear();
            //如果存在数据就一行一行读取
            while (rs.next()) {
                Book book = new Book();
                book.setTitle(rs.getString(1));
                book.setBookid(Integer.parseInt(rs.getString(2)));
                book.setIsbn(rs.getString(3));
                book.setAuthor(rs.getString(4));
                book.setCategory(rs.getString(5));
                list.add(book);
            }
            st.close();
            conn.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public Book getBoodid(String bookid) {
        Book book = new Book();
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);

            //3.构造命令并执行命令
            String sql = "select * from students where stuNo = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, bookid);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                book.setTitle(rs.getString("title"));
                book.setBookid(Integer.parseInt(rs.getString("bookid")));
                book.setIsbn(rs.getString("isbn"));
                book.setAuthor(rs.getString("author"));
                book.setCategory(rs.getString("category"));
            }
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return book;
    }

    @Override
    public void updateBood(Book book) {
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);

            //3.构造命令并执行命令
            String sql = "update books set bookis = ?, isbn = ?, title = ?, author = ?, category = ?WHERE bookid = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, book.getBookid());
            ps.setString(2, book.getIsbn());
            ps.setString(3, book.getTitle());
            ps.setString(4, book.getAuthor());
            ps.setString(5, book.getCategory());
            ps.executeUpdate();
            ps.close();
            conn.close();

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void addBook(Book book) {
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);

            //3.构造命令并执行命令
            String sql = "insert into books(bookid,isbn,title,author,category)values(?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, book.getBookid());
            ps.setString(2, book.getIsbn());
            ps.setString(3, book.getTitle());
            ps.setString(4, book.getAuthor());
            ps.setString(5, book.getCategory());
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public void deleteBook(String bookid) {
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);

            //3.构造命令并执行命令
            String sql = "DELETE FROM books WHERE bookid = '" +bookid+"'";
            Statement st = conn.createStatement();
            st.executeUpdate(sql);
            st.close();
            conn.close();

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


}
